package scct.ZhuZhouXing.dao;

import scct.ZhuZhouXing.model.Staff;
import scct.ZhuZhouXing.utils.DBUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class StaffDao {
    public static List<Staff> getallstaff() {
        List<Staff> list = new ArrayList();
        Connection conn = DBUtils.getConnection();
        String sql = "select * from sp_staff";
        try {
            PreparedStatement pstm = conn.prepareStatement(sql);//连接对象获取数据库操作对象
            ResultSet rs = pstm.executeQuery();//执行sql语句
            while (rs.next()) {
                int aid = rs.getInt("aid");
                String ausername = rs.getString("ausername");
                String gender = rs.getString("gender");
                int age = rs.getInt("age");
                String position = rs.getNString("position");
                String phone = rs.getNString("phone");
                String salary = rs.getString("salary");
                list.add(new Staff(aid, ausername, gender, age, position, phone, salary));
            }
            DBUtils.closeConnection(conn);//关闭数据库
        } catch (SQLException var13) {
        }

        return list;
    }

    public boolean addStaff(String ausername, String gender, int age, String position, String phone, String salary) {
        int result = 0;
        Connection conn = DBUtils.getConnection();
        String insert_sql = "insert into sp_staff values(null,?,?,?,?,?,?)";
        try {
            PreparedStatement pstmt = conn.prepareStatement(insert_sql);
            pstmt.setString(1, ausername);
            pstmt.setString(2, gender);
            pstmt.setInt(3, age);
            pstmt.setString(4, position);
            pstmt.setString(5, phone);
            pstmt.setString(6, salary);
            result = pstmt.executeUpdate();
        } catch (SQLException var11) {
            throw new RuntimeException(var11);
        }

        return result == 1;
    }

    public boolean deStaff(int id) {
        int num = 0;
        Connection conn = DBUtils.getConnection();
        String sp_sql = "delete from sp_staff where aid=?";
        try {
            PreparedStatement pstmt = conn.prepareStatement(sp_sql);
            pstmt.setInt(1, id);
            num = pstmt.executeUpdate();
            DBUtils.closeConnection(conn);
        } catch (SQLException var6) {
            throw new RuntimeException(var6);
        }
        return num > 0;
    }

    public boolean updateStaff(int aid, String ausername, String gender, int age, String position, String phone, String salary) {
        int result = 0;
        String sql = "update sp_staff set ausername=?,gender=?,age=?,position=?,phone=?,salary=? where aid=?";
        Connection conn = DBUtils.getConnection();
        try {
            PreparedStatement pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, ausername);
            pstmt.setString(2, gender);
            pstmt.setInt(3, age);
            pstmt.setString(4, position);
            pstmt.setString(5, phone);
            pstmt.setString(6, salary);
            pstmt.setInt(7, aid);
            result = pstmt.executeUpdate();
            DBUtils.closeConnection(conn);
        } catch (SQLException var12) {
            throw new RuntimeException(var12);
        }

        return result > 0;
    }

    public boolean isDupname(String phone) {
        int count = 0;
        Connection conn = DBUtils.getConnection();
        String sql = "select * from sp_staff where phone=?";
        try {
            PreparedStatement pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, phone);
            ResultSet rs = pstmt.executeQuery();
            if (rs.next()) {
                count = 1;
            }
            DBUtils.closeConnection(conn);
        } catch (SQLException var7) {
            throw new RuntimeException(var7);
        }

        return count > 0;
    }

    public Staff getUserById(int id) {
        Staff staff = null;
        Connection conn = DBUtils.getConnection();
        String sql = "select * from sp_staff where aid = ?;";
        try {
            PreparedStatement pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, id);
            ResultSet rs = pstmt.executeQuery();
            if (rs.next()) {
                String ausername = rs.getString("ausername");
                String gender = rs.getString("gender");
                int age = rs.getInt("age");
                String position = rs.getString("position");
                String phone = rs.getString("phone");
                String salary = rs.getString("salary");
                staff = new Staff(id, ausername, gender, age, position, phone, salary);
            }

            DBUtils.closeConnection(conn);
            return staff;
        } catch (SQLException var13) {
            throw new RuntimeException(var13);
        }
    }

    public List<Staff> getselect(String name) {
        List<Staff> list = new ArrayList();
        Connection conn = DBUtils.getConnection();
        String sql = "select * from sp_staff where ausername like '%" + name + "%'";
        try {
            PreparedStatement pstmt = conn.prepareStatement(sql);
            ResultSet rs = pstmt.executeQuery();

            while (rs.next()) {
                int aid = rs.getInt("aid");
                String aname = rs.getString(2);
                String agender = rs.getString("gender");
                int aage = rs.getInt("age");
                String aposition = rs.getString("position");
                String aphone = rs.getString("phone");
                String asalary = rs.getNString("salary");
                list.add(new Staff(aid, aname, agender, aage, aposition, aphone, asalary));
            }
            DBUtils.closeConnection(conn);
            return list;
        } catch (SQLException var14) {
            throw new RuntimeException(var14);
        }
    }

    public List<Staff> getScreen(String gender) {
        List<Staff> list = new ArrayList();
        Connection conn = DBUtils.getConnection();
        String sql = "select * from sp_staff where gender like '%" + gender + "%'";
        try {
            PreparedStatement pstmt = conn.prepareStatement(sql);
            ResultSet rs = pstmt.executeQuery();

            while (rs.next()) {
                int aid = rs.getInt("aid");
                String aname = rs.getString("name");
                String agender = rs.getString("3");
                int aage = rs.getInt("age");
                String aposition = rs.getString("position");
                String aphone = rs.getString("phone");
                String asalary = rs.getNString("salary");
                list.add(new Staff(aid, aname, agender, aage, aposition, aphone, asalary));
            }
            DBUtils.closeConnection(conn);
            return list;
        } catch (SQLException var14) {
            throw new RuntimeException(var14);
        }
    }

    public boolean delete(int aid) {
        Integer count = 0;
        Connection conn = DBUtils.getConnection();
        String sql = "delete from sp_staff where aid=?";

        try {
            PreparedStatement ps = conn.prepareStatement(sql);
            ps.setInt(1, aid);
            count = count + ps.getUpdateCount();
            DBUtils.closeConnection(conn);
        } catch (SQLException var6) {
            throw new RuntimeException(var6);
        }

        return count > 0;
    }
}
